package top.zk123.chain.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import top.zk123.chain.bean.ExcelData;
import top.zk123.chain.exception.ExcelNotFoundException;
import top.zk123.chain.exception.ExcelParseErrorException;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel 解析类
 *
 * @author Ke Zhang
 * @since 2.0
 */
public class ExcelReader {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名获取对应的工作薄对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名
     * @return 包含文件数据的工作薄对象
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) {
        Workbook workbook = null;
        try {
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return workbook;
    }

    /**
     * 将单元格内容转化为字符串
     *
     * @param cell 单元格
     * @return String
     */
    public static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case STRING:    // 字符串
                returnValue = cell.getStringCellValue();
                break;
            case BLANK:     // 空值:
            case ERROR:     // 故障
                break;
            case BOOLEAN:   // 布尔值
                returnValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:   // 公式:
                returnValue = cell.getCellFormula();
                break;
            case NUMERIC:
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(cell.getNumericCellValue());
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 提取每一行的数据，构造成一个结果数据对象
     *
     * @param row 行数据
     * @return 结果数据对象
     * @throws ExcelParseErrorException Excel解析失败
     */
    public static ExcelData convertRowToData(Row row) throws ExcelParseErrorException {
        ExcelData resultData = new ExcelData();
        int cellNum = 0;
        String id = convertCellValueToString(row.getCell(cellNum++));
        if (id == null) {
            throw new ExcelParseErrorException("序号不得为空");
        }
        resultData.setId(id);
        String certType = convertCellValueToString(row.getCell(cellNum++));
        if (certType == null) {
            throw new ExcelParseErrorException("证书类型不得为空");
        }
        resultData.setCertType(certType);
        String name = convertCellValueToString(row.getCell(cellNum++));
        if (name == null) {
            throw new ExcelParseErrorException("姓名不得为空");
        }
        resultData.setName(name);
        String sex = convertCellValueToString(row.getCell(cellNum++));
        if (sex == null) {
            throw new ExcelParseErrorException("性别不得为空");
        }
        resultData.setSex(sex);
        String birth = convertCellValueToString(row.getCell(cellNum++));
        if (birth == null) {
            throw new ExcelParseErrorException("生日不得为空");
        }
        resultData.setBirth(birth);
        String userId = convertCellValueToString(row.getCell(cellNum++));
        if (userId == null) {
            throw new ExcelParseErrorException("身份证号不得为空");
        }
        resultData.setUserId(userId);
        String academy = convertCellValueToString(row.getCell(cellNum++));
        if (academy == null) {
            throw new ExcelParseErrorException("学院不得为空");
        }
        resultData.setAcademy(academy);
        String major = convertCellValueToString(row.getCell(cellNum++));
        if (major == null) {
            throw new ExcelParseErrorException("专业不得为空");
        }
        resultData.setMajor(major);
        String education = convertCellValueToString(row.getCell(cellNum++));
        if (education == null) {
            throw new ExcelParseErrorException("学历不得为空");
        }
        resultData.setEducation(education);
        String admission = convertCellValueToString(row.getCell(cellNum++));
        if (admission == null) {
            throw new ExcelParseErrorException("入学时间不得为空");
        }
        resultData.setAdmissionDate(admission);
        String graduate = convertCellValueToString(row.getCell(cellNum++));
        if (graduate == null) {
            throw new ExcelParseErrorException("毕业时间不得为空");
        }
        resultData.setGraduationDate(graduate);
        String certId = convertCellValueToString(row.getCell(cellNum++));
        if (certId == null) {
            throw new ExcelParseErrorException("证书编号不得为空");
        }
        resultData.setCertId(certId);
        String issueDate = convertCellValueToString(row.getCell(cellNum++));
        if (issueDate == null) {
            throw new ExcelParseErrorException("证书颁发日期不得为空");
        }
        resultData.setIssueDate(issueDate);
        String institution = convertCellValueToString(row.getCell(cellNum++));
        if (institution == null) {
            throw new ExcelParseErrorException("颁发单位不得为空");
        }
        resultData.setInstitution(institution);
        String userPic = convertCellValueToString(row.getCell(cellNum++));
        if (userPic == null) {
            throw new ExcelParseErrorException("一寸照名称不得为空");
        }
        resultData.setUserPic(userPic);
        String certPic = convertCellValueToString(row.getCell(cellNum));
        if (certPic == null) {
            throw new ExcelParseErrorException("证书扫描件名称不得为空");
        }
        resultData.setCertPic(certPic);
        return resultData;
    }

    /**
     * 解析 Excel 数据
     *
     * @param workbook Excel 工作薄
     * @return List {@link ExcelData}
     * @throws ExcelParseErrorException Excel解析失败
     */
    public static List<ExcelData> parseExcel(Workbook workbook) throws ExcelParseErrorException {
        List<ExcelData> resultDataList = new ArrayList<>();
        // 解析 sheet
        int tmp = workbook.getNumberOfSheets();
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null) {
                continue;
            }
            // 第一行数据所在行数
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                throw new ExcelParseErrorException();
            }
            // 解析后续每一行
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                ExcelData resultData = convertRowToData(row);
                resultDataList.add(resultData);
            }
        }
        return resultDataList;
    }

    /**
     * 解析 Excel 文件
     *
     * @param fileName 路径
     * @return List 实体类 {@link ExcelData}
     * @throws ExcelNotFoundException   文件不存在
     * @throws ExcelParseErrorException Excel解析失败
     */
    public static List<ExcelData> readExcel(String fileName) throws ExcelNotFoundException,
            ExcelParseErrorException {
        List<ExcelData> resultDataList = new ArrayList<>();
        Workbook workbook;
        FileInputStream is;

        try {
            String fileType = GlobalUtils.getExtension(fileName);
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                throw new ExcelNotFoundException();
            }
            // 获取工作薄
            is = new FileInputStream(excelFile);
            workbook = getWorkbook(is, fileType);
            // 读取 Excel 数据
            resultDataList = parseExcel(workbook);
            workbook.close();
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return resultDataList;
    }
}